----------------------------------------------------- Start - Surveys -----------------------------------

---------- Surveys_GetAll
-- 0 - show All
-- 1 - show Surveys
-- 2 - show Templates

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_GetAll]
GO

CREATE PROCEDURE [dbo].[Surveys_GetAll]
	@template integer,
	@type integer
AS
	SELECT
		*
	FROM
		[dbo].[Surveys]
	where 
		ISNULL(DELETED, 0) != 1 AND
		(@type is null or [TYPE] = @type) AND
		(@template is null or TEMPLATE = @template)
	ORDER BY
		NAME ASC
GO

GRANT EXEC ON [dbo].[Surveys_GetAll] TO [DomainUsers], [QrdUser]
GO


/*
EXEC [dbo].[Surveys_GetAll] null, null
*/


---------- Surveys_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_GetByID]
GO

CREATE PROCEDURE [dbo].[Surveys_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Surveys]
	WHERE
		ID = @id
GO

GRANT EXEC ON [dbo].[Surveys_GetByID] TO [DomainUsers], [QrdUser]
GO

/*
EXEC [dbo].[Surveys_GetByID] 1
*/


---------- Surveys_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Create]
GO

CREATE PROCEDURE [dbo].[Surveys_Create]
	@name varchar(100),
	@isTemplate integer,
	@type integer,
	@templateID integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[Surveys]
	(
		[NAME],
		[TYPE],
		[TEMPLATE]
	)
	VALUES
	(
		LTRIM(RTRIM(@name)),
		@type,
		@isTemplate
	)

	SET @id = SCOPE_IDENTITY();

	-- duplicate data of the template survey
	IF ( @templateID > 0 ) 
	BEGIN
		exec Surveys_Duplicate_Categories @templateID, @id;
	END
		
GO

GRANT EXEC ON [dbo].[Surveys_Create] TO [DomainUsers], [QrdUser]
GO

/*
DECLARE @id integer
EXEC [dbo].[Surveys_Create] 'Direct', @id=@id OUTPUT
SELECT @id
*/


---------- Surveys_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Surveys_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Surveys_Update]
GO

CREATE PROCEDURE [dbo].[Surveys_Update]
	@id integer,
	@type integer,
	@name varchar(100)
AS
	UPDATE
		[dbo].[Surveys]
	SET
		[NAME] = LTRIM(RTRIM(@name)),
		[TYPE] = @type
	WHERE
		ID = @id
GO

GRANT EXEC ON [dbo].[Surveys_Update] TO [DomainUsers], [QrdUser]
GO

-- exec [dbo].[Survey_GetCountNumberOfAnswers] 68

------[SurveySessions_GetAvailableConversationSurveysForUser]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAvailableConversationSurveysForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAvailableConversationSurveysForUser]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAvailableConversationSurveysForUser]
	@survey_by varchar(100),
	@conversation_guid uniqueidentifier
AS
	SELECT
		Surveys.ID,
		Surveys.NAME,
		Surveys.DELETED,
		Surveys.TEMPLATE,
		Surveys.[TYPE]
	FROM
		[dbo].[Surveys]
	WHERE
		[TYPE] = 0 AND
		Surveys.ID NOT IN
		(
			SELECT 
				SurveySessions.SURVEY_ID
			FROM
				[dbo].[SurveySessions]
			WHERE
				SURVEY_BY = @survey_by
			AND 
				CONVERSATION_GUID = @conversation_guid
		) 
	AND
		ISNULL(DELETED, 0) != 1
GO

------[SurveySessions_GetAvailableDocumentsSurveysForUser]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAvailableDocumentsSurveysForUser]
	@survey_by varchar(100),
	@document_id varchar(20)
AS
	SELECT
		Surveys.ID,
		Surveys.NAME,
		Surveys.DELETED,
		Surveys.TEMPLATE,
		Surveys.[TYPE]
	FROM
		[dbo].[Surveys]
	WHERE
		[TYPE] = 1 AND
		Surveys.ID NOT IN
		(
			SELECT 
				SurveySessions.SURVEY_ID
			FROM
				[dbo].[SurveySessions]
			WHERE
				SURVEY_BY = @survey_by
			AND 
				DOCUMENT_ID = @document_id
		)
	AND
		ISNULL(DELETED, 0) != 1
GO

GRANT EXEC ON [dbo].[SurveySessions_GetAvailableSurveysForUser] TO [DomainUsers], [QrdUser]
GO


-- exec [dbo].[SurveySessions_GetAvailableSurveysForUser] "ESS\Ildikob", "0fac3a34-862d-4a85-b654-811f930ee2af"

-- SurveySessions_GetAllConversationSurveys
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAllConversationSurveys]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAllConversationSurveys]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAllConversationSurveys]
	@conversation_guid uniqueidentifier
AS
	SELECT
		[dbo].[Surveys].ID as surveyID,
		[dbo].[Surveys].NAME,
		[dbo].[Surveys].TEMPLATE,
		[dbo].[Surveys].[TYPE],
		[dbo].[SurveySessions].OCCURRED,
		[dbo].[SurveySessions].ID as sessionID,
		[dbo].[SurveySessions].SURVEY_ID,
		[dbo].[SurveySessions].CONVERSATION_GUID,
		[dbo].[SurveySessions].DOCUMENT_ID,
		[dbo].[SurveySessions].SURVEY_STOP,
		[dbo].[SurveySessions].SURVEY_START,
		[dbo].[SurveySessions].SURVEY_CONTINUE,
		[dbo].[SurveySessions].IS_COMPLETED,
		[dbo].[ADUsers].LOGON_NAME,
		[dbo].[ADUsers].FIRST_NAME,
		[dbo].[ADUsers].LAST_NAME
	FROM
		[dbo].[Surveys]
	INNER JOIN
		[dbo].[SurveySessions] ON Surveys.ID = SurveySessions.SURVEY_ID
	INNER JOIN
		[dbo].[ADUsers] ON SurveySessions.SURVEY_BY = ADUsers.LOGON_NAME
	WHERE
		SurveySessions.CONVERSATION_GUID = @conversation_guid
	AND
		ISNULL(DELETED, 0) != 1
GO

GRANT EXEC ON [dbo].[SurveySessions_GetAllConversationSurveys] TO [DomainUsers], [QrdUser]
GO


-- SurveySessions_GetAllDocumentSurveys
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveySessions_GetAllDocumentSurveys]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[SurveySessions_GetAllDocumentSurveys]
GO

CREATE PROCEDURE [dbo].[SurveySessions_GetAllDocumentSurveys]
	@document_id varchar(20)
AS
	SELECT
		[dbo].[Surveys].ID as surveyID,
		[dbo].[Surveys].NAME,
		[dbo].[Surveys].TEMPLATE,
		[dbo].[Surveys].[TYPE],
		[dbo].[SurveySessions].OCCURRED,
		[dbo].[SurveySessions].ID as sessionID,
		[dbo].[SurveySessions].SURVEY_ID,
		[dbo].[SurveySessions].CONVERSATION_GUID,
		[dbo].[SurveySessions].DOCUMENT_ID,
		[dbo].[SurveySessions].SURVEY_STOP,
		[dbo].[SurveySessions].SURVEY_START,
		[dbo].[SurveySessions].SURVEY_CONTINUE,
		[dbo].[SurveySessions].IS_COMPLETED,
		[dbo].[ADUsers].LOGON_NAME,
		[dbo].[ADUsers].FIRST_NAME,
		[dbo].[ADUsers].LAST_NAME
	FROM
		[dbo].[Surveys]
	INNER JOIN
		[dbo].[SurveySessions] ON Surveys.ID = SurveySessions.SURVEY_ID
	INNER JOIN
		[dbo].[ADUsers] ON SurveySessions.SURVEY_BY = ADUsers.LOGON_NAME
	WHERE
		SurveySessions.DOCUMENT_ID = @document_id
	AND
		ISNULL(DELETED, 0) != 1
GO

GRANT EXEC ON [dbo].[SurveySessions_GetAllDocumentSurveys] TO [DomainUsers], [QrdUser]
GO

-- select * from [dbo].[SurveySessions] order by OCCURRED desc
-- update [dbo].[SurveySessions] set SURVEY_BY = 'danrm' where SURVEY_BY = 'chrisc' order by OCCURRED desc
-- select * from ADUsers where LOGON_NAME like '%danrm%'
-- exec [dbo].[SurveySessions_GetAllDocumentSurveys] 1
----------------------------------------------------- END - Surveys -------------------------------------

----------------------------------------------------- Start - DocumentClasses -----------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[docuDocClasses_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[docuDocClasses_GetAll]
GO

CREATE PROCEDURE [dbo].[docuDocClasses_GetAll]
AS
	SELECT
		*
	FROM
		[Socrates].[dbo].[docuDocClasses]
	ORDER BY
		LetterType ASC
GO

